lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-09-04.txt

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainPhotographed')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainPhotographed
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainPhotographed]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainPhotographed]
AS
SELECT  tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type,
dbo.tb_ErpOrdersPhotography.ID AS VID, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
(Ord_SeriesPrice - (Case when (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) is null then 0 else (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) end) + (Select sum(Plu_Amount) as Plu_Amount from tb_ErpPlusPickItems where Plu_OrdNumber = Ord_Number)) AS 欠款,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpOrdersPhotography.Ordpg_Sights AS 拍摄名称, 
dbo.fn_CheckPhotographyStatus(Ordpg_PhotographyStatus) AS 拍摄状态,
dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime) AS 摄影时间, 
Ordpg_ReservationPhotographyTime AS 摄影时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyName) AS 摄影师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyAssistant) AS 摄影助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupArtist) AS 化妆师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupAssistant) AS 化妆助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationBootDivision) AS 引导师, 
dbo.tb_ErpOrdersPhotography.Ordpg_Remark AS 录入备注, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_Dispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrdersPhotography.Ordpg_DispatchTime)) AS 安排时间,
(SELECT COUNT(tb_ErpOrdersPhotography_1.Ordpg_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 LEFT OUTER JOIN
dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON tb_ErpOrdersPhotography_1.Ordpg_ViceNumber = tb_ErpOrderDigital_1.Ordv_ViceNumber WHERE (tb_ErpOrderDigital_1.Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainSelectDress')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainSelectDress
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainSelectDress]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainSelectDress]
AS
SELECT   
tb_ErpOrder.ID,
Ord_DividedShop, 
Ord_Type, 
Ord_Number, 
Ordv_ViceNumber,
Ordv_DigitalNumber, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
dbo.fn_CheckOrderClothingStatus(Ordv_ClothingStatus) AS 选礼服状态, 
dbo.fn_CheckUserIDGetUserName(Ordv_ReservationClothingName) AS 选礼服师, 
dbo.fn_CheckDateTime(Ordv_ReservationClothingTime) AS 选礼服时间,  
Ordv_ReservationClothingTime AS 选礼服时间查询, 
dbo.fn_CheckUserIDGetUserName(Ordv_ClothingDispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_ClothingDispatchTime)) AS 安排时间,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number
left join tempTB_AggregationCustomer on Ord_Number = GP_OrderNumber
WHERE   (Ord_Class = ''1'') AND (Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainWedding')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainWedding
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainWedding]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainWedding]
AS
SELECT   
tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Type,
dbo.tb_ErpWeddingService.Ws_Type, 
dbo.tb_ErpWeddingService.ID AS VID, 
dbo.tb_ErpOrder.Ord_Number, 
0 AS Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,  
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpWeddingService.Ws_ProdName AS 服务内容, 
dbo.fn_CheckWeddingServiceStatus(dbo.tb_ErpWeddingService.Ws_Status) AS 服务状态, 
dbo.fn_CheckDateTime(Ws_WeddingDate) AS 服务日期, 
Ws_WeddingDate AS 服务日期查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpWeddingService.Ws_Serviceman1) AS 服务人员1, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpWeddingService.Ws_Serviceman2) AS 服务人员2,
dbo.tb_ErpWeddingService.Ws_Remark AS 服务备注,
(SELECT COUNT(Ws_Number) AS Expr1 FROM dbo.tb_ErpWeddingService AS tb_ErpWeddingService_1 WHERE (Ws_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM    dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpWeddingService ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpWeddingService.Ws_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') 
' 
GO